package com.example.test_activity.dao;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.test_activity.entity.Score;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by 旭 on 2017/3/23/023.
 */

public class DBScore extends SQLiteOpenHelper{

    public static DBScore helper;

    public static DBScore instance(Context context)
    {
        if (helper == null)
        {
            helper = new DBScore(context);
        }
        return helper;
    }

    private DBScore(Context context)
    {
        this(context,"score.db",null,1);
    }

    private DBScore(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
    {
        super(context,name,factory,version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String scoreCreateSql = "create table score (Id integer primary key autoincrement," +
                "name varchar," +
                "credit varchar," +
                "score varchar," +
                "reason varchar)";
        db.execSQL(scoreCreateSql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void saveScore(Score score)
    {
        String sql = "insert into score (name,credit,score,reason) values (?,?,?,?)";
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL(sql,new Object[] {score.getName(),score.getCredit(),score.getScore(),score.getReason()});
    }

    public List<Map<String,String>> getAllScore()
    {
        deleteSpilthData();
        List<Map<String,String>> list = new ArrayList<>();
        SQLiteDatabase db = getWritableDatabase();
        String sql = "select name,credit,score,reason from score";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext())
        {
            Map<String,String> map = new HashMap<>();
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String credit = cursor.getString(cursor.getColumnIndex("credit"));
            String score = cursor.getString(cursor.getColumnIndex("score"));
            String reason = cursor.getString(cursor.getColumnIndex("reason"));
            map.put("name",name);
            map.put("credit",credit);
            map.put("score",score);
            map.put("reason",reason);
            list.add(map);
        }
        return list;
    }

    //
    public void deleteSpilthData()
    {
        SQLiteDatabase db = getWritableDatabase();
        String sql = "delete from score where name in (select name from score group by name having count(name)>1)" +
                "and Id not in (select min(Id) from score group by name having count(name) > 1)";
        db.execSQL(sql);
    }

}
